﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Web.Security;
using System.Data.SqlClient;

namespace SuperStarProject.DAO
{
    public class DataProvider
    {
        public static SqlConnection DBconnection()
        {
            // 1. Instantiate the connection
            SqlConnection conn = new SqlConnection(
                "Data Source=.;Initial Catalog=SuperStar7;Integrated Security=True");
            conn.Open();
            return conn;
        }
        // run a simple stored procedure
        public static SqlDataReader RunStoredProc_Read(string namesp, SqlConnection conn)
        {
            SqlDataReader rdr = null;

            SqlCommand cmd = new SqlCommand(namesp, conn);

            cmd.CommandType = CommandType.StoredProcedure;

            rdr = cmd.ExecuteReader();

            return rdr;
        }
        // run a stored procedure that takes a parameter
        public static SqlDataReader RunStoredProcParams_Read(string namesp, SqlConnection conn, List<SqlParameter> lstparameter)
        {
            SqlDataReader rdr = null;

            // 1.  create a command object identifying
            //     the stored procedure
            SqlCommand cmd = new SqlCommand(namesp, conn);

            cmd.CommandType = CommandType.StoredProcedure;

            // 3. add parameter to command, which
            //    will be passed to the stored procedure
            foreach(SqlParameter x in lstparameter)
            {
                cmd.Parameters.Add(x);
            }
            // execute the command
            rdr = cmd.ExecuteReader();

            return rdr;
        }
        public static int RunStoredProcParams_NoRead(string namesp, SqlConnection conn, List<SqlParameter> lstparameter)
        {
            // 1.  create a command object identifying
            //     the stored procedure
            SqlCommand cmd = new SqlCommand(namesp, conn);

            cmd.CommandType = CommandType.StoredProcedure;

            // 3. add parameter to command, which
            //    will be passed to the stored procedure
            foreach (SqlParameter x in lstparameter)
            {
                cmd.Parameters.Add(x);
            }
            return cmd.ExecuteNonQuery();
        }
        public static string RunStoredProcParams_ReadOutPut(string namesp, SqlConnection conn, List<SqlParameter> lstparameter, string nameparameter)
        {
            // 1.  create a command object identifying
            //     the stored procedure
            SqlCommand cmd = new SqlCommand(namesp, conn);

            cmd.CommandType = CommandType.StoredProcedure;

            // 3. add parameter to command, which
            //    will be passed to the stored procedure
            foreach (SqlParameter x in lstparameter)
            {
                cmd.Parameters.Add(x);
            }
            cmd.ExecuteReader();
            // execute the command
            return cmd.Parameters[nameparameter].Value.ToString();
        }

    }
}
